package com.css.fxfzcpxx.modules.logRecord.repository.impl;


import com.css.fxfzcpxx.modules.logRecord.entity.SysLog;
import com.css.fxfzcpxx.modules.logRecord.repository.LogRecordRepository;
import com.css.fxfzcpxx.modules.systemOperation.vo.StatisticsDataVo;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:
 * @CreateTime: 2023/9/11 10:56
 * @Description:
 */
@Repository
public class LogRecordRepositoryImpl implements LogRecordRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void save(SysLog sysLog) {
        String sql = "insert into sys_log(log_id,op_type,op_user,op_time,modules,data_type,page_view,sign) " +
                "values(:logId, :opType, :opUser, :opTime, :modules,:dataType, :pageView, :sign)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(sysLog);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public SysLog findByDataType(String dataType) {
        String sql="SELECT * FROM sys_log WHERE op_time::date = current_date and data_type=:dataType";
        Map<String,String> param = new HashMap<>();
        param.put("dataType",dataType);
//        try {
//            sysLog = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SysLog.class) );
//        }catch (Exception exception){
//            return null;
//        }
        try {
            return namedParameterJdbcTemplate.queryForObject(sql, param, new BeanPropertyRowMapper<>(SysLog.class));
        }catch (Exception exception){
            return null;
        }
    }


    @Override
    public void updatePageView(String logId, Integer pageView) {
        List<Object> params = new ArrayList<Object>();
        params.add(pageView);
        params.add(logId);
        String sql = "update sys_log set page_view = ? where log_id = ?";
        jdbcTemplate.update(sql,params.toArray());
    }

    @Override
    public SysLog findByModuleAndType(String modules, String type) {
        String sql="SELECT * FROM sys_log WHERE op_time::date = current_date and modules='"+modules+"' and op_type='"+type+"'";
        SysLog sysLog=new SysLog();
        try {
            sysLog = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SysLog.class) );
        }catch (Exception exception){
            return null;
        }

        return sysLog;
    }

    @Override
    public Integer getTotalPageView(String opType) {
        String sql = "select sum(page_view) from sys_log where op_type='"+opType+"' and sign='0'";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }

    @Override
    public List<StatisticsDataVo> getPageViewStatisticsData(String opType,String unit) {
        String sql = "";
        if("year".equals(unit)){
            sql ="WITH months AS ( " +
                    "    SELECT " +
                    "        generate_series ( " +
                    "            date_trunc('month', CURRENT_DATE) - INTERVAL '11 months', " +
                    "            date_trunc('month', CURRENT_DATE), " +
                    "            INTERVAL '1 month' " +
                    "        ) AS month_start " +
                    ") " +
                    "SELECT " +
                    "    to_char(months.month_start, 'YYYY-MM') AS unit, " +
                    "    COALESCE(SUM(sys_log.page_view), 0) AS pageview " +
                    "FROM " +
                    "    months " +
                    "LEFT JOIN sys_log ON  " +
                    "    EXTRACT(YEAR FROM sys_log.op_time) = EXTRACT(YEAR FROM months.month_start)  " +
                    "    AND EXTRACT(MONTH FROM sys_log.op_time) = EXTRACT(MONTH FROM months.month_start) " +
                    "    AND sys_log.op_type = '"+opType+"' " +
                    "    AND sys_log.sign = '0' " +
                    "GROUP BY " +
                    "    months.month_start " +
                    "ORDER BY " +
                    "    months.month_start;";
        }else{
            sql ="WITH DateSeries AS ( " +
                    "    SELECT " +
                    "        generate_series ( " +
                    "            DATE_TRUNC('day', CURRENT_DATE - INTERVAL '29 days'), " +
                    "            DATE_TRUNC('day', CURRENT_DATE), " +
                    "            '1 day'::INTERVAL " +
                    "        ) AS DAY " +
                    ") " +
                    "SELECT " +
                    "    TO_CHAR(ds.DAY, 'YYYY-MM-DD') AS unit, " +
                    "    COALESCE(SUM(sl.page_view), 0) AS pageView " +
                    "FROM " +
                    "    DateSeries ds " +
                    "LEFT JOIN sys_log sl ON DATE_TRUNC('day', sl.op_time) = ds.DAY  " +
                    "    AND sign = '0'  " +
                    "    AND op_type = '"+opType+"' " +
                    "GROUP BY " +
                    "    ds.DAY " +
                    "ORDER BY " +
                    "    ds.DAY;";
        }

        List<StatisticsDataVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(StatisticsDataVo.class));

        return list;
    }

    @Override
    public List<Map<String,Object>> getRetrievalVolumeStatisticsData(String opType, String unit) {
        String sql = "select modules as name,sum(page_view) as value from sys_log where op_type='检索' and sys_log.sign='0' " +
                "AND EXTRACT("+unit+" FROM op_time) = EXTRACT("+unit+" FROM CURRENT_DATE) " +
                "group by modules ";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);


        return list;
    }

    @Override
    public List<StatisticsDataVo> getPageViewRankingData(String opType,String unit) {
        String sql = "";
        if("year".equals(unit)){
            sql = "SELECT data_type modules, SUM(page_view) AS pageView " +
                    "FROM sys_log " +
                    "WHERE op_type = '"+opType+"' AND sys_log.sign = '0' " +
                    "AND EXTRACT("+unit+" FROM op_time) = EXTRACT("+unit+" FROM CURRENT_DATE) " +
                    "GROUP BY data_type " +
                    "ORDER BY pageView DESC";
        }else{
            sql = "SELECT data_type AS modules, SUM(page_view) AS pageView " +
                    "FROM sys_log " +
                    "WHERE op_type = '"+opType+"' " +
                    "AND sys_log.sign = '0' " +
                    "AND op_time >= CURRENT_DATE - INTERVAL '30 days' " +
                    "GROUP BY data_type " +
                    "ORDER BY pageView DESC";
        }
        List<StatisticsDataVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(StatisticsDataVo.class));

        return list;
    }
}
